package com.palrevo.data.dao;

import java.io.ByteArrayInputStream;
import java.io.ObjectInputStream;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.palrevo.data.models.Model;
import com.palrevo.utils.Log;

/**
 * 
 * @author AAsha
 */
public class DAOImpl implements DAO {

	private List<DaoRecord> records;

	/**
     *
     */
	public DAOImpl() {
	}

	/**
     * 
     */
	@Override
	public int insert(Model model, Map<Integer, Object> params) throws DataException {

		try {
			String stmt = QueryBuilder.createStatement(QueryBuilder.STMT_INSERT, model.getTableName(), model.getFields(), null);
			return this.executeUpdate(params, stmt);
		} catch (Exception e) {
			Log.error(this, e.getMessage(), e);
			throw new DataException(e.getMessage(), e.getCause());
		}
	}

	/**
	 * 
	 * @param obj
	 */
	@Override
	public int update(Model model, List<String> keys, Map<Integer, Object> params) throws DataException {

		try {
			String stmt = QueryBuilder.createStatement(QueryBuilder.STMT_UPDATE, model.getTableName(), model.getFields(), keys);
			return this.executeUpdate(params, stmt);
		} catch (Exception e) {
			Log.error(this, e.getMessage(), e);
			throw new DataException(e.getMessage(), e.getCause());
		}
	}

	/**
     * 
     */
	@Override
	public int delete(Model model) throws DataException {

		try {
			String stmt = QueryBuilder.createStatement(QueryBuilder.STMT_DELETE, model.getTableName(), model.getFields(), null);
			return this.executeUpdate(stmt);
		} catch (Exception e) {
			Log.error(this, e.getMessage(), e);
			throw new DataException(e.getMessage(), e.getCause());
		}
	}

	@Override
	public void select(String tableName, List<String> fields) throws DataException {

		try {
			String stmt = QueryBuilder.createStatement(QueryBuilder.STMT_SELECT, tableName, fields, null);
			this.execute(stmt);
		} catch (Exception e) {
			Log.error(this, e.getMessage(), e);
		}
	}
	
	@Override
	public void select(String tableName, String... fields) throws DataException {
		
		try {
			String stmt = QueryBuilder.createStatement(QueryBuilder.STMT_SELECT, tableName, Arrays.asList(fields), null);
			this.execute(stmt);
		} catch (Exception e) {
			Log.error(this, e.getMessage(), e);
		}
	}
	
	@Override
	public void selectWhere(String tableName, List<String> fields, List<String> keys, Map<Integer, Object> params) throws DataException {

		try {
			String stmt = QueryBuilder.createStatement(QueryBuilder.STMT_SLCT_WHERE, tableName, fields, keys);
			this.executeQuery(params, stmt);
		} catch (Exception e) {
			Log.error(this, e.getMessage(), e);
		}
	}
	
	@Override
	public void selectCount(String tableName, List<String> fields) throws DataException {

		try {
			String stmt = QueryBuilder.createStatement(QueryBuilder.STMT_SELECT_COUNT, tableName, fields, null);
			this.execute(stmt);
		} catch (Exception e) {
			Log.error(this, e.getMessage(), e);
		}
	}

	@Override
	public void execute(String stmt) throws DataException {
		executeQuery(new HashMap<Integer, Object>(), stmt);
	}
	
	@Override
	public int executeUpdate(String stmt) throws DataException {
		return (executeUpdate(new HashMap<Integer, Object>(0), stmt));
	}
	
	@Override
	public List<DaoRecord> getResult() {
		return records;
	}

	/**
	 * 
	 * Execute an update query and return integer represent number of effected
	 * rows.
	 * 
	 * @param params
	 * @param sql
	 * @throws DataException
	 */
	public synchronized int executeUpdate(Map<Integer, Object> params,
			String sql) throws DataException {

		// parameters cannot be null
		if (params == null) {
			Log.error(this, "Params Map Connot be null !");
			throw new DataException("Params Map Connot be null !");
		}

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		conn = ConnectionManager.getConnection();

		try {
			pstmt = conn.prepareStatement(sql);

			// replace ? with values from keys
			for (int i = 1; i <= params.size(); i++) {
				pstmt.setObject(i, params.get(i));
			}

			// execute and return result set
			return pstmt.executeUpdate();

		} catch (SQLException ex) {

			Log.error(this, "Error Excuting Query !");
			throw new DataException(ex.getMessage());

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}

			} catch (SQLException ex) {
				Log.error(this, "Error Closing RS or PSTMTT !");
				throw new DataException(ex.getMessage());
			}

			ConnectionManager.freeConnection(conn);
		}
	}

	/**
	 * Execute a query and cache returned Result Set.
	 * 
	 * @param params
	 * @param sql
	 * @throws DataException
	 */
	@Override
	public synchronized void executeQuery(Map<Integer, Object> params,
			String sql) throws DataException {

		// parameters cannot be null
		if (params == null) {
			Log.error(this, "Params Map Connot be null !");
			throw new DataException("Params Map Connot be null !");
		}

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		conn = ConnectionManager.getConnection();

		try {
			pstmt = conn.prepareStatement(sql);

			// replace ? with values from keys
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(++i, params.get(i));
			}

			// execute and return result set
			rs = pstmt.executeQuery();

			if (rs != null) {
				cacheAll(rs);
			}

		} catch (SQLException ex) {

			Log.error(this, "Error Excuting Query !");
			throw new DataException(ex.getMessage());

		} finally {
			try {
				
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}

			} catch (SQLException ex) {
				Log.error(this, "Error Closing RS or PSTMTT !");
				throw new DataException(ex.getMessage());
			}

			ConnectionManager.freeConnection(conn);
		}
	}

	/**
	 * Caches all data returned in the given resultset.
	 * 
	 * @param rs
	 *            ResultSet to read from
	 * @throws DataException
	 *             if an error occurred while processing the ResultSet
	 */
	private void cacheAll(ResultSet rs) throws DataException {
		
		try {
			
			int columnCount = rs.getMetaData().getColumnCount();

			records = new ArrayList<DaoRecord>();

			while (rs.next()) {
				records.add(this.getNextDaoRecord(columnCount, rs));
			}

		} catch (Exception ex) {
			Log.error(this, "Error Caching ResultSet !");
			throw new DataException("Error Caching ResultSet !");
		}
	}

	/**
	 * Get Next DaoRecord
	 * 
	 * @param numOfCols
	 * @param rs
	 * @return
	 * @throws DataException
	 */
	private DaoRecord getNextDaoRecord(int numOfCols, ResultSet rs)
			throws Exception {

		DaoRecord newRec = new DaoRecord();
		ResultSetMetaData metaData = rs.getMetaData();

		for (int i = 1; i <= numOfCols; i++) {

			String columnName = metaData.getColumnLabel(i);
			Object value = rs.getObject(columnName);
			
			if (value instanceof Blob) {
				value = this.readBlob(rs, columnName);
			}
			
			if (value instanceof Clob) {
				value = this.readClob(value);
			}

			newRec.addValue(columnName, value);
		}
		return newRec;
	}
	
	/**
	 * Read Blob objects
	 */
	private Object readBlob(ResultSet rs, String columnName) throws DataException {
        
		ByteArrayInputStream bais;
        ObjectInputStream oins;
        Object obj;
		
        try {
            bais = new ByteArrayInputStream(rs.getBytes(columnName));
            oins = new ObjectInputStream(bais);
            obj = oins.readObject();
            bais.close();
		} catch (Exception ex) {
			Log.error(this, "Error reading Blob !");
			throw new DataException(ex.getMessage());
		}
        
        return obj;
	}

	/**
	 * convert CLOB data object to String
	 */
	private String readClob(Object ob) throws DataException {
		
		Clob clob = (Clob) ob;
		StringBuffer bufCLOB = new StringBuffer();
		
		char[] buffer = new char[4000];
		
		try {
			
			Reader clobReader = clob.getCharacterStream();
			
			int len = 0;
			while ((len = clobReader.read(buffer)) != -1) {
				bufCLOB.append(buffer, 0, len);
			}
			
			clobReader.close();
			
		} catch (Exception ex) {
			Log.error(this, "Error reading Clob !");
			throw new DataException(ex.getMessage());
		}

		return bufCLOB.toString();
	}

}
